﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using DBHelper;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;
namespace Repository
{
    public class SqlUserRepository : IRepository.IUserRepository
    {

        public bool Exists(string id)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select count(*) from BaseUser where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            int i = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbQuery.ToString(), parameters));
            return i > 0 ? true : false;
        }

        public bool Add(User user)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            user.Password = BitConverter.ToString(md5.ComputeHash(Encoding.Default.GetBytes(user.Password)));

            StringBuilder sbInsert = new StringBuilder();
            sbInsert.Append("insert into BaseUser(ID,UserName,Password,Phone,Email,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark)");
            sbInsert.Append("values(@ID,@UserName,@Password,@Phone,@Email,@Creator,@CreateTime,@Updator,@UpdateTime,@Enabled,@Remark)");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.VarChar,36),
                    new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Password", SqlDbType.NVarChar,50),
                    new SqlParameter("@Phone", SqlDbType.VarChar,20),
                    new SqlParameter("@Email", SqlDbType.NVarChar,100),
                    new SqlParameter("@Creator", SqlDbType.NVarChar,50),
                    new SqlParameter("@CreateTime", SqlDbType.DateTime),
                    new SqlParameter("@Updator", SqlDbType.NVarChar,50),
                    new SqlParameter("@UpdateTime", SqlDbType.DateTime),
                    new SqlParameter("@Enabled", SqlDbType.Bit,1),
                    new SqlParameter("@Remark", SqlDbType.NVarChar,200)};
            parameters[0].Value = user.ID;
            parameters[1].Value = user.UserName;
            parameters[2].Value = user.Password;
            parameters[3].Value = user.Phone;
            parameters[4].Value = user.Email;
            parameters[5].Value = user.Creator;
            parameters[6].Value = user.CreateTime;
            parameters[7].Value = user.Updator;
            parameters[8].Value = user.UpdateTime;
            parameters[9].Value = user.Enabled;
            parameters[10].Value = user.Remark;

            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbInsert.ToString(), parameters);
            return i > 0 ? true : false;

        }

        public bool Update(User user)
        {
            StringBuilder sbUpdate = new StringBuilder();
            sbUpdate.Append("update BaseUser set ");
            sbUpdate.Append("UserName=@UserName,");
            sbUpdate.Append("Phone=@Phone,");
            sbUpdate.Append("Email=@Email,");
            sbUpdate.Append("Updator=@Updator,");
            sbUpdate.Append("UpdateTime=@UpdateTime,");
            sbUpdate.Append("Enabled=@Enabled,");
            sbUpdate.Append("Remark=@Remark");
            sbUpdate.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Phone", SqlDbType.VarChar,20),
                    new SqlParameter("@Email", SqlDbType.NVarChar,100),
                    new SqlParameter("@Updator", SqlDbType.NVarChar,50),
                    new SqlParameter("@UpdateTime", SqlDbType.DateTime),
                    new SqlParameter("@Enabled", SqlDbType.Bit,1),
                    new SqlParameter("@Remark", SqlDbType.NVarChar,200),
                    new SqlParameter("@ID", SqlDbType.VarChar,36)};
            parameters[0].Value = user.UserName;
            parameters[1].Value = user.Phone;
            parameters[2].Value = user.Email;
            parameters[3].Value = user.Updator;
            parameters[4].Value = user.UpdateTime;
            parameters[5].Value = user.Enabled;
            parameters[6].Value = user.Remark;
            parameters[7].Value = user.ID;
            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbUpdate.ToString(), parameters);
            return i > 0 ? true : false;
        }

        public bool Delete(string id)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseUser where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), parameters);
            return i > 0 ? true : false;
        }

        public bool Delete(List<string> listID)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseUser where ID in(");
            for (int i = 0; i < listID.Count; i++)
            {
                if (i == 0)
                {
                    sbDelete.Append("'" + listID[i] + "'");
                }
                else
                {
                    sbDelete.Append(",'" + listID[i] + "'");
                }
            }
            sbDelete.Append(")");
            int j = SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), null);
            return j > 0 ? true : false;
        }

        public User Get(string id)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select ID,UserName,Password,Phone,Email,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark from BaseUser");
            sbQuery.Append(" where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            User user = new User();
            while (dr.Read())
            {
                user = DataReaderToUser(dr);
            }
            dr.Close();
            return user;
        }

        public IQueryable<User> GetList(string strWhere)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select ID,UserName,Password,Phone,Email,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark from BaseUser");
            sbQuery.Append(" where (" + strWhere + ")");
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), null);
            List<User> listUsers = new List<User>();
            while (dr.Read())
            {
                listUsers.Add(DataReaderToUser(dr));
            }
            return listUsers.AsQueryable();
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select count(*) from BaseUser");
            sbQuery.Append(" where (" + strWhere + ")");
            return Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbQuery.ToString(), null));
        }

        public IQueryable<User> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            strWhere = String.IsNullOrEmpty(strWhere.Trim()) ? "1=1" : strWhere;
            orderby = String.IsNullOrEmpty(orderby.Trim()) ? "UserName desc" : orderby;
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select t.*from(");
            sbQuery.AppendFormat("    select ROW_NUMBER() OVER(order by p.{0}) as RowNum, p.* from BaseUser p", orderby);
            sbQuery.AppendFormat("	where ({0})", strWhere);
            sbQuery.Append(") t");
            sbQuery.AppendFormat(" where t.RowNum between {0} and {1}", startIndex, endIndex);
            List<User> listUsers = new List<User>();
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), null);
            while (dr.Read())
            {
                listUsers.Add(DataReaderToUser(dr));

            }
            dr.Close();
            return listUsers.AsQueryable();
        }

        public bool Vefify(string UserName, string password)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            password = BitConverter.ToString(md5.ComputeHash(Encoding.Default.GetBytes(password)));
            string strWhere = "UserName='" + UserName + "' and Password='" + password + "'";
            int i = GetRecordCount(strWhere);
            return i > 0;
        }
        public User Get(string UserName, string password)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            password = BitConverter.ToString(md5.ComputeHash(Encoding.Default.GetBytes(password)));

            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select ID,UserName,Password,Phone,Email,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark from BaseUser");
            sbQuery.Append(" where (UserName=@UserName and Password=@Password)");
            SqlParameter[] parameters = { 
                    new SqlParameter("@UserName", SqlDbType.NVarChar, 50), 
                    new SqlParameter("@Password", SqlDbType.NVarChar, 50)
           };
            parameters[0].Value = UserName;
            parameters[1].Value = password;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            User user = new User();
            while (dr.Read())
            {
                user = DataReaderToUser(dr);
            }
            dr.Close();
            return user;
        }
        private User DataReaderToUser(SqlDataReader dr)
        {
            Entities.User model = new Entities.User();
            if (dr != null)
            {
                if (dr["ID"] != null)
                {
                    model.ID = dr["ID"].ToString();
                }
                if (dr["UserName"] != null)
                {
                    model.UserName = dr["UserName"].ToString();
                }
                if (dr["Password"] != null)
                {
                    model.Password = dr["Password"].ToString();
                }
                if (dr["Phone"] != null)
                {
                    model.Phone = dr["Phone"].ToString();
                }
                if (dr["Email"] != null)
                {
                    model.Email = dr["Email"].ToString();
                }
                if (dr["Creator"] != null)
                {
                    model.Creator = dr["Creator"].ToString();
                }
                if (dr["CreateTime"] != null && dr["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                }
                if (dr["Updator"] != null)
                {
                    model.Updator = dr["Updator"].ToString();
                }
                if (dr["UpdateTime"] != null && dr["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
                }
                if (dr["Enabled"] != null && dr["Enabled"].ToString() != "")
                {
                    if ((dr["Enabled"].ToString() == "1") || (dr["Enabled"].ToString().ToLower() == "true"))
                    {
                        model.Enabled = true;
                    }
                    else
                    {
                        model.Enabled = false;
                    }
                }
                if (dr["Remark"] != null)
                {
                    model.Remark = dr["Remark"].ToString();
                }
            }
            return model;
        }
    }
}
